#!/bin/bash

# 生产管理系统视图备份脚本
# 使用方法: ./backup_views.sh

# 数据库配置
DB_HOST="127.0.0.1"
DB_USER="scanwork"
DB_PASS="rDezxK9h7tWp5sP7"
DB_PORT="3306"
DB_NAME="scanwork"

# 创建备份目录
BACKUP_DIR="/www/wwwroot/scanwork/backup_views"
mkdir -p "$BACKUP_DIR"

# 备份时间戳
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

echo "开始备份视图..."

# 1. 备份视图结构
echo "备份视图结构..."
mysqldump -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -P "$DB_PORT" \
  --no-data \
  --routines \
  --triggers \
  --where="TABLE_TYPE='VIEW'" \
  "$DB_NAME" > "$BACKUP_DIR/views_structure_$TIMESTAMP.sql"

# 2. 生成视图重建脚本
echo "生成视图重建脚本..."
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -P "$DB_PORT" "$DB_NAME" -e "
SELECT CONCAT('DROP VIEW IF EXISTS ', TABLE_NAME, ';') as sql_statement
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = '$DB_NAME' 
AND TABLE_NAME IN ('v_quality_statistics', 'v_employee_workload', 'v_production_progress');
" > "$BACKUP_DIR/drop_views_$TIMESTAMP.sql"

# 3. 获取视图创建语句
echo "获取视图创建语句..."
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -P "$DB_PORT" "$DB_NAME" -e "
SELECT CONCAT('CREATE VIEW ', TABLE_NAME, ' AS ', VIEW_DEFINITION, ';') as sql_statement
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = '$DB_NAME' 
AND TABLE_NAME IN ('v_quality_statistics', 'v_employee_workload', 'v_production_progress');
" > "$BACKUP_DIR/create_views_$TIMESTAMP.sql"

# 4. 创建完整的重建脚本
echo "创建完整重建脚本..."
cat > "$BACKUP_DIR/restore_views_$TIMESTAMP.sql" << 'EOF'
-- 生产管理系统视图重建脚本
-- 使用方法: mysql -u username -p database_name < restore_views_TIMESTAMP.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 删除现有视图
DROP VIEW IF EXISTS v_quality_statistics;
DROP VIEW IF EXISTS v_employee_workload;
DROP VIEW IF EXISTS v_production_progress;

EOF

# 添加视图创建语句
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" -P "$DB_PORT" "$DB_NAME" -e "
SELECT CONCAT('CREATE VIEW ', TABLE_NAME, ' AS ', VIEW_DEFINITION, ';') as sql_statement
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = '$DB_NAME' 
AND TABLE_NAME IN ('v_quality_statistics', 'v_employee_workload', 'v_production_progress');
" >> "$BACKUP_DIR/restore_views_$TIMESTAMP.sql"

echo "SET FOREIGN_KEY_CHECKS = 1;" >> "$BACKUP_DIR/restore_views_$TIMESTAMP.sql"

# 5. 创建说明文件
cat > "$BACKUP_DIR/README_$TIMESTAMP.txt" << EOF
生产管理系统视图备份说明
========================

备份时间: $(date)
数据库: $DB_NAME

文件说明:
---------
1. views_structure_TIMESTAMP.sql - 视图结构备份
2. drop_views_TIMESTAMP.sql - 删除视图语句
3. create_views_TIMESTAMP.sql - 创建视图语句  
4. restore_views_TIMESTAMP.sql - 完整重建脚本
5. README_TIMESTAMP.txt - 本说明文件

使用方法:
---------
在新环境中重建视图:
mysql -u username -p database_name < restore_views_TIMESTAMP.sql

重要提醒:
---------
1. 确保所有依赖的表已经存在
2. 确保数据库用户有创建视图的权限
3. 视图依赖以下表:
   - fa_scanwork_report
   - fa_scanwork_employee
   - fa_scanwork_allocation
   - fa_scanwork_order
   - fa_scanwork_production_plan

视图功能:
---------
- v_quality_statistics: 质量统计报表
- v_employee_workload: 员工工作量统计
- v_production_progress: 生产进度跟踪
EOF

echo "备份完成!"
echo "备份文件保存在: $BACKUP_DIR"
echo "主要文件: restore_views_$TIMESTAMP.sql"
